package AdminController.AdminAce;

import java.sql.Connection;
import java.util.*;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import AdminController.AdminExe.AdminExeBean;
import Connect.connector;

public class AdminAceptDAO {

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	public AdminAceBean[] yearList(int list,Date start ,Date end ){
		conn = connector.getConnection();
		ArrayList<AdminAceBean> result = new ArrayList<AdminAceBean>();
		
		java.sql.Date Nstart = new java.sql.Date(start.getTime());
		java.sql.Date Nend = new java.sql.Date(end.getTime());
		
		try{
			
			String sql = "select count(ID)as cnt,date_format(Mem_Date,'%y/%m') from MEMBERS where Mem_Level = ? and Mem_Date >= ? and Mem_Date <= ? group by date_format(Mem_Date,'%Y/%m')";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,list);
			pstmt.setDate(2,Nstart);
			pstmt.setDate(3,Nend);
			
			rs = pstmt.executeQuery();

			while(rs.next()){
				
				AdminAceBean aAbean =  new AdminAceBean();
				aAbean.setCount(rs.getInt(1));
				aAbean.setMem_Date(rs.getString(2));
				result.add(aAbean);
			
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminAceBean[0]);
	}
	
	public AdminAceBean[] monthList(int list,String year ,String month ){
		conn = connector.getConnection();
		ArrayList<AdminAceBean> result = new ArrayList<AdminAceBean>();
		
		try{
			
			String sql = "select count(ID)as cnt,date_format(Mem_Date,'%m/%d') from MEMBERS where Mem_Level = ? and date_format(Mem_Date,'%Y')=? and date_format(Mem_Date,'%m')=? group by date_format(Mem_Date,'%m/%d')";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1,list);
			pstmt.setString(2,year);
			pstmt.setString(3,month);
			
			rs = pstmt.executeQuery();

			while(rs.next()){
				
				AdminAceBean aAbean =  new AdminAceBean();
				aAbean.setCount(rs.getInt(1));
				aAbean.setMem_Date(rs.getString(2));
				result.add(aAbean);
			
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminAceBean[0]);
	}
	
	public AdminAceBean[] monthRecList(String year ,String month ){
		conn = connector.getConnection();
		ArrayList<AdminAceBean> result = new ArrayList<AdminAceBean>();
		
		try{
			
			String sql = "select count(ID)as cnt,date_format(Old_Date,'%m/%d') from OLDPLUS where date_format(Old_Date,'%Y')=? and date_format(Old_Date,'%m')=? group by date_format(Old_Date,'%m/%d')";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1,year);
			pstmt.setString(2,month);
			
			rs = pstmt.executeQuery();

			while(rs.next()){
				
				AdminAceBean aAbean =  new AdminAceBean();
				aAbean.setCount(rs.getInt(1));
				aAbean.setOld_Date(rs.getString(2));
				result.add(aAbean);
			
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminAceBean[0]);
	}
	
	
	public AdminAceBean[] ReYearList(String year){
		conn = connector.getConnection();
		ArrayList<AdminAceBean> result = new ArrayList<AdminAceBean>();
		
		try{
			
			String sql = "select count(ID)as cnt,date_format(Old_Date,'%y/%m') from OLDPLUS where date_format(Old_Date,'%Y')=?  group by date_format(Old_Date,'y/%m')";
			
			pstmt = conn.prepareStatement(sql);
			
		
			pstmt.setString(1,year);
			
			
			rs = pstmt.executeQuery();

			while(rs.next()){
				
				AdminAceBean aAbean =  new AdminAceBean();
				aAbean.setCount(rs.getInt(1));
				aAbean.setOld_Date(rs.getString(2));
				result.add(aAbean);
			
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminAceBean[0]);
	}
	
	public AdminAceBean[] ReYearYList(String year){
		conn = connector.getConnection();
		ArrayList<AdminAceBean> result = new ArrayList<AdminAceBean>();
		
		try{
			
			String sql = "select count(ID)as cnt,date_format(Bby_Date,'%y/%m') from BABYPLUS where date_format(Bby_Date,'%Y')=?  group by date_format(Bby_Date,'y/%m')";
			
			pstmt = conn.prepareStatement(sql);
			
		
			pstmt.setString(1,year);
			
			
			rs = pstmt.executeQuery();

			while(rs.next()){
				
				AdminAceBean aAbean =  new AdminAceBean();
				aAbean.setCount(rs.getInt(1));
				aAbean.setOld_Date(rs.getString(2));
				result.add(aAbean);
			
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminAceBean[0]);
	}

	public AdminAceBean[] monthRecYList(String year ,String month ){
		conn = connector.getConnection();
		ArrayList<AdminAceBean> result = new ArrayList<AdminAceBean>();
		
		try{
			
			String sql = "select count(ID)as cnt,date_format(Bby_Date,'%m/%d') from BABYPLUS where date_format(Bby_Date,'%Y')=? and date_format(Bby_Date,'%m')=? group by date_format(Bby_Date,'%m/%d')";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1,year);
			pstmt.setString(2,month);
			
			rs = pstmt.executeQuery();

			while(rs.next()){
				
				AdminAceBean aAbean =  new AdminAceBean();
				aAbean.setCount(rs.getInt(1));
				aAbean.setOld_Date(rs.getString(2));
				result.add(aAbean);
			
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			connector.releaseConnection(conn, pstmt, rs);
		}
		return result.toArray(new AdminAceBean[0]);
	}
	

}
